# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
from xlutils.copy import copy
import os
import sys

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function:企名片标签 验证
"""

def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook(r'qimingpianTagCount2018-7-13.xlsx')
    sheet2_name = workbook.sheet_names()[1]

    # 根据sheet索引或者名称获取sheet内容
    sheet2 = workbook.sheet_by_name('Sheet2')

    # sheet的名称，行数，列数
    # print sheet2.name,sheet2.nrows,sheet2.ncols
    nrows = sheet2.nrows    # 获取行总数
    ncols = sheet2.ncols    #获取列总数

    mapDict = {}
    #循环打印每一行的内容
    for i in range(1,nrows):
        print sheet2.row_values(i)
        for j in range(4,ncols):
            closValue = sheet2.cell_value(i,1)
            closValueResult = sheet2.cell_value(i,3)
            if closValueResult == 'CV 标签 V1.0已存在' or closValueResult == '保留':
                mapDict.setdefault(closValue,set()).add(sheet2.cell_value(i,1))
                break
            elif closValueResult == '改名':
                if sheet2.cell_value(i,j) == "":
                    break
                else:
                    mapDict.setdefault(closValue,set()).add(sheet2.cell_value(i,j))

    searchQimingpian(mapDict)

def searchQimingpian(mapDict):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "test", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT COUNT(a.id) a FROM `qimingpian_product` a WHERE a.portray !='' ORDER BY cv_id DESC "
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/50
    # for index in range(pageCount):
    for index in range(123,4930):
    #上次跑到
        limitStart = index * 50
        print("limitStart====================================================",limitStart)

        #查询qimingpian中企业和对应标签信息
        qimingpianSql = "SELECT cv_id,company_name,portray,'' AS beizhu,'' AS excelValue,'' AS tianyanchaName " \
                    "FROM qimingpian_product WHERE portray !='' ORDER BY cv_id DESC limit %d,50 "%limitStart

        cursor.execute(qimingpianSql)
        qimingpianResults1 = cursor.fetchall()
        fields = cursor.description
        #根据cvid获取清洗后结果做对比
        compareQimingpianTags(qimingpianResults1, mapDict, fields)

    db.close()

def compareQimingpianTags(qimingpianResults1, mapDict, fields):
    db = MySQLdb.connect("rm-2zenl5bol3h6o6hqllo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6", "cv_data_info", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()
    for qimingpianIndex in range(len(qimingpianResults1)):
        qimingpianCvId = qimingpianResults1[qimingpianIndex].get(u"cv_id")
        print("qimingpianCvId:"+str(qimingpianCvId))
        qimingpianCompanyName = qimingpianResults1[qimingpianIndex].get(u"company_name")
        qimingpianTag = qimingpianResults1[qimingpianIndex].get(u"portray")
        qimingpianTag = qimingpianTag[1:(len(qimingpianTag)-1)]
        qimingpianTagStr = qimingpianTag.split(",")
        for index in range(len(qimingpianTagStr)):
            qimingpianTag = qimingpianTagStr[index]
            qimingpianTag = qimingpianTag[1:(len(qimingpianTag)-1)]
            #qimingpian中的tag拆分循环去excel中规则找对应结果，找到后看dc_company_tag清洗结果表中此企业是否打对了标签
            if qimingpianTag in mapDict.keys():
                excelValueList = list(mapDict[qimingpianTag])
                for excelValue in excelValueList:
                    # tianyanchaSql = "SELECT cv_id,company_name,tag_name FROM dc_company_tag " \
                    #                 "WHERE `status`=1 AND cv_id ='%s' AND tag_name='%s'" %(qimingpianCvId,excelValue)

                    tianyanchaSql = "SELECT cv_id,company_name,tag_name FROM dc_company_tag " \
                                    "WHERE `status`=1 AND company_name ='%s' AND tag_name='%s'" %(qimingpianCompanyName,excelValue)

                    cursor.execute(tianyanchaSql)
                    tianyanchaResults1 = cursor.fetchall()

                    if len(tianyanchaResults1)==0:
                           #此sql是为了拼dc_company_tag表中的企业打上的所有标签，只为了向excel中写数据时候用到
                           tianyanchaTagNameSql = "SELECT cv_id,GROUP_CONCAT(tag_name) AS tianyanchaName " \
                                                  "FROM dc_company_tag WHERE `status`=1 AND cv_id = '%s' GROUP BY cv_id " %(qimingpianCvId)

                           cursor.execute(tianyanchaTagNameSql)
                           tianyanchaTagNameResults1 = cursor.fetchall()
                           if len(tianyanchaTagNameResults1) == 0:
                               qimingpianResults1[qimingpianIndex].update(beizhu=u'天眼查dc_company_tag表没有此条企业标签信息')
                               qimingpianResults1[qimingpianIndex].update(excelValue=excelValue)
                               qimingpianResults1[qimingpianIndex].update(tianyanchaName='')
                               writeExcel(qimingpianResults1[qimingpianIndex], fields)
                           else:
                               tianyanchaName = tianyanchaTagNameResults1[0].get(u"tianyanchaName")
                               qimingpianResults1[qimingpianIndex].update(beizhu=u'洗后的企业标签与对应的规则值不完全匹配')
                               qimingpianResults1[qimingpianIndex].update(excelValue=excelValue)
                               qimingpianResults1[qimingpianIndex].update(tianyanchaName=tianyanchaName)
                               writeExcel(qimingpianResults1[qimingpianIndex], fields)
            else:
                qimingpianResults1[qimingpianIndex].update(beizhu=u'此标签在规则内没有：' + qimingpianTag)
                qimingpianResults1[qimingpianIndex].update(excelValue='')
                qimingpianResults1[qimingpianIndex].update(tianyanchaName='')
                writeExcel(qimingpianResults1[qimingpianIndex], fields)

    db.close()

def writeExcel(results,fields):

    fileName = 'dataQimingpianTagResult.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            # print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
